package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.DTRequestDto;
import com.hualude.roadsign.dto.DTSolarFaultDto;
import com.hualude.roadsign.dto.SolarEnergyFaultDto;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by ding on 2017/7/10.
 */
public class SolarFaultRepositoryImpl {
    @PersistenceContext
    private EntityManager em;

    public List<DTSolarFaultDto> getSolarFault4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId) throws Exception {
        String sql = "SELECT solarFaultId,sf.solarEnergyId,solarPhoto,solarCreatorId,solarTime,solarCheckerId," +
                "solarLevel,solarType,solarReason,solarMoney,sf.SolarFeedback,\n" +
                "solarMainCheckerId,solarCheck,solarMainCheck,solarOrgId,solarMainOrgId,OrgName AS solarOrgName,solarEnergyPhoto \n" +
                "FROM tb_solar_fault sf,tb_org o,tb_solar_energy se \n" +
                "WHERE sf.SolarOrgId=o.OrgId AND sf.SolarEnergyId=se.SolarEnergyId  " +
                " AND o.orgId like '%" + dtRequestDto.getSearch() + "%' AND sf.SolarCheck=1 ";
        //根据主管审核人和系统管理员区分
        if (actorId == 7) {
            sql += " AND sf.SolarMainOrgId=" + orgId + "";
        }
        sql += "  AND sf.solarMainCheck IN (0,1,-1) \n" +
                " order by  find_in_set(sf.solarMainCheck ,'0,1,-1') , sf.solarTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTSolarFaultDto.class));
        List<DTSolarFaultDto> solarFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return solarFaultDtos;
    }

    public Integer getSolarFaultCount4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId) throws Exception {
        String sql = "SELECT count(*)\n" +
                "FROM tb_solar_fault sf,tb_org o,tb_solar_energy se \n" +
                "WHERE sf.SolarOrgId=o.OrgId AND sf.SolarEnergyId=se.SolarEnergyId  " +
                " AND o.orgId like '%" + dtRequestDto.getSearch() + "%' AND sf.SolarCheck=1 ";
        if (actorId == 7) {
            sql += " AND sf.SolarMainOrgId=" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<DTSolarFaultDto> getSolarHandle4DT(DTRequestDto dtRequestDto, String actorIds, Integer orgId, Integer solarMaintenanceStatus) throws Exception {
        String sql = "SELECT u.userName as solarCreatorName,solarFaultId,OrgName AS solarOrgName,SolarCreatorId,solarTime,solarCheckerId,solarType,solarLevel,solarMaintenancePhoto,solarMaintainerId," +
                "solarReason,solarEnergyLongitude,solarEnergyLatitude,solarMaintenanceStatus,solarMainCheckTime,solarMaintenanceTime,solarEnergyRoadName,solarMaintenanceExplain," +
                "solarMainCheckerId,sf.solarEnergyId,solarEnergyStartRoad,solarEnergyEndRoad,solarMaintenanceContent,solarFeedback," +
                "solarPhoto,solarEnergyPhoto,SolarEnergyArea,SolarEnergyCity,SolarEnergyProvince,solarOrgId,solarMoney\n" +
                "FROM tb_solar_energy se,tb_solar_fault sf,tb_org o,tb_user u\n" +
                "WHERE sf.SolarEnergyId=se.SolarEnergyId AND u.userId=sf.solarCreatorId AND sf.SolarOrgId=o.OrgId AND sf.SolarMainCheck in (1,2) " +
                "AND concat(se.SolarEnergyRoadName, se.solarEnergyStartRoad, se.solarEnergyEndRoad) like '%" + dtRequestDto.getSearch() + "%' AND sf.SolarCheck in (1,2) ";
        //根据维修人员和系统管理员区分
        if (solarMaintenanceStatus != 3) {
            sql += " and sf.solarMaintenanceStatus = " + solarMaintenanceStatus + " ";
        }

        if (actorIds.indexOf('1') >= 0) {
            sql += "";
        } else if (actorIds.indexOf('3') >= 0 || actorIds.indexOf('5') >= 0) {
            sql += " AND sf.SolarOrgId =" + orgId + "";
        } else if (actorIds.indexOf('7') >= 0) {
            sql += " AND sf.SolarMainOrgId =" + orgId + "";
        }
        sql += " order by sf.solarMaintenanceStatus ASC ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTSolarFaultDto.class));
        List<DTSolarFaultDto> solarFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return solarFaultDtos;
    }

    public Integer getSolarHandleCount4DT(DTRequestDto dtRequestDto, String actorIds, Integer orgId, Integer solarMaintenanceStatus) throws Exception {
        String sql = "SELECT count(*)\n" +
                "FROM tb_solar_energy se,tb_solar_fault sf,tb_org o\n" +
                "WHERE sf.SolarEnergyId=se.SolarEnergyId AND sf.SolarOrgId=o.OrgId AND sf.SolarMainCheck=1 " +
                "AND concat(se.SolarEnergyRoadName, se.solarEnergyStartRoad, se.solarEnergyEndRoad) like '%" + dtRequestDto.getSearch() + "%' AND sf.SolarCheck=1 ";
        //根据维修人员和系统管理员区分
        if (solarMaintenanceStatus != 3) {
            sql += " and sf.solarMaintenanceStatus = " + solarMaintenanceStatus + " ";
        }
        if (actorIds.indexOf('1') >= 0) {
            sql += "";
        } else if (actorIds.indexOf('3') >= 0 || actorIds.indexOf('5') >= 0) {
            sql += " AND sf.SolarOrgId =" + orgId + "";
        } else if (actorIds.indexOf('7') >= 0) {
            sql += " AND sf.SolarMainOrgId =" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public Integer getSolarFaultCount4DT(Integer actorId, Integer userId, Integer orgId, String strSearch) throws Exception {
        String sql = "select count(*) " +
                "FROM (((tb_solar_fault sof JOIN tb_user u ON u.UserId=sof.SolarCreatorId)JOIN tb_org o ON o.OrgId=sof.SolarOrgId)\n" +
                "JOIN tb_solar_energy soe ON soe.SolarEnergyId=sof.SolarEnergyId)\n" +
                "where concat(soe.SolarEnergyRoadName,soe.SolarEnergyStartRoad,soe.SolarEnergyEndRoad)like '%" + strSearch + "%'";
        if (actorId == 3) {
            sql += " and sof.SolarOrgId=" + orgId + "";
        } else if (actorId == 6) {
            sql += " and sof.solarCreatorId=" + userId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<DTSolarFaultDto> getSolarFault4DT(DTRequestDto dtRequestDto, Integer actorId, Integer userId, Integer orgId) throws Exception {
        String sql = "SELECT sof.*,u.UserName AS solarCreatorName,u2.UserName AS solarMaintainerName,u3.UserName " +
                "AS solarMainCheckerName, o.OrgName AS solarOrgName,se.solarEnergyPhoto from tb_solar_fault sof left join tb_user u on" +
                " u.UserId=sof.SolarCreatorId left join tb_user u2 on u2.userId = sof.SolarMaintainerId " +
                "left join tb_user u3 on u3.userId=sof.SolarMainCheckerId left join tb_org o on o.orgId=sof.SolarOrgId " +
                "left join tb_solar_energy se on se.SolarEnergyId=sof.SolarEnergyId " +
                "WHERE concat(se.SolarEnergyRoadName,se.SolarEnergyStartRoad,se.SolarEnergyEndRoad)like '%" + dtRequestDto.getSearch() + "%' ";
        if (actorId == 3) {
            sql += " and sof.SolarOrgId=" + orgId + "";
        } else if (actorId == 6) {
            sql += " and sof.solarCreatorId=" + userId + "";
        }
        sql += " AND sof.SolarCheck IN (0,1,2,-1) \n" +
                " order by find_in_set( sof.SolarCheck ,'0,2,1,-1') , sof.SolarTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTSolarFaultDto.class));
        List<DTSolarFaultDto> signfaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return signfaultDtos;
    }

    public Integer getSolarFaultReportCount4DT(String search, Integer actorId, Integer orgId, String solarEnergyStartRoad, String solarEnergyEndRoad, String solarEnergyRoadName) throws Exception {
        String sql = "select count(*) " +
                "FROM tb_solar_energy se,tb_org o,tb_user u\n" +
                "WHERE se.SolarEnergyOrgId =o.OrgId AND u.UserId =se.SolarEnergyCreatorId and se.SolarEnergyRoadName like '%" + solarEnergyRoadName + "%' " +
                " and se.SolarEnergyStartRoad like '%" + solarEnergyStartRoad + "%' and se.SolarEnergyEndRoad like '%" + solarEnergyEndRoad + "%' ";
        if (actorId != 1) {
            sql += " and se.SolarEnergyOrgId=" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<DTSolarFaultDto> getSolarFaultReport4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId, String solarEnergyStartRoad, String solarEnergyEndRoad, String solarEnergyRoadName) throws Exception {
        String sql = "SELECT se.SolarEnergyId,se.SolarEnergyPhoto,se.SolarEnergyProvince,se.SolarEnergyCity,se.SolarEnergyArea,se.SolarEnergyRegionId,o.OrgName AS solarOrgName,u.UserName AS solarCreatorName,se.SolarEnergyInstallTime\n" +
                "FROM tb_solar_energy se,tb_org o,tb_user u\n" +
                "WHERE se.SolarEnergyOrgId =o.OrgId AND u.UserId =se.SolarEnergyCreatorId and se.SolarEnergyRoadName like '%" + solarEnergyRoadName + "%' " +
                " and se.SolarEnergyStartRoad like '%" + solarEnergyStartRoad + "%' and se.SolarEnergyEndRoad like '%" + solarEnergyEndRoad + "%' ";
        if (actorId != 1) {
            sql += " and se.SolarEnergyOrgId=" + orgId + "";
        }
        if (solarEnergyStartRoad != "" || solarEnergyEndRoad != "") {
            sql += " UNION SELECT se.SolarEnergyId,se.SolarEnergyPhoto,se.SolarEnergyProvince,se.SolarEnergyCity,se.SolarEnergyArea,se.SolarEnergyRegionId,o.OrgName AS solarOrgName,u.UserName AS solarCreatorName,se.SolarEnergyInstallTime\n" +
                    "FROM tb_solar_energy se,tb_org o,tb_user u\n" +
                    "WHERE se.SolarEnergyOrgId =o.OrgId AND u.UserId =se.SolarEnergyCreatorId and se.SolarEnergyRoadName like '%" + solarEnergyRoadName + "%' " +
                    " and se.SolarEnergyStartRoad like '%" + solarEnergyEndRoad + "%' and se.SolarEnergyEndRoad like '%" + solarEnergyStartRoad + "%' ";
        }
        if (actorId != 1) {
            sql += " and se.SolarEnergyOrgId=" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTSolarFaultDto.class));
        List<DTSolarFaultDto> signfaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return signfaultDtos;
    }

    public List<SolarEnergyFaultDto> getSolarFaultCheckList4App(Integer orgId, String actorIds, Integer faultStatus,
                                                                Integer count, Integer size) throws Exception {
        String sql = "SELECT sof.SolarFaultId,sof.SolarEnergyId,sof.SolarPhoto,sof.SolarType,sof.SolarLevel,sof.SolarReason," +
                "sof.SolarTime,sof.SolarCheck,sof.SolarMainCheck,sof.SolarMainCheckTime\n" +
                "FROM tb_solar_fault sof";
        if (actorIds.indexOf("1") >= 0) {//系统管理员
            if (faultStatus == 0) {//待审核
                sql += " WHERE (sof.SolarCheck =0) OR (sof.SolarMainCheck =0 AND sof.SolarCheck=1 )  ";
            } else if (faultStatus == 1) {//已通过
                sql += " WHERE  sof.SolarMainCheck =1 AND sof.SolarCheck=1  ";
            } else if (faultStatus == 2) {//未通过
                sql += " WHERE ( sof.SolarCheck =-1) OR (sof.SolarMainCheck =-1 AND sof.SolarCheck=1 ) ";
            } else {
                return null;
            }
        } else if (actorIds.indexOf("3") >= 0) {//审核员
            if (faultStatus == 0) {//待审核
                sql += " WHERE sof.SolarCheck=0 AND sof.SolarOrgId=" + orgId + " ";
            } else if (faultStatus == 1) {//已通过
                sql += " WHERE sof.SolarCheck=1 AND sof.SolarOrgId=" + orgId + " ";
            } else if (faultStatus == 2) {//未通过
                sql += " WHERE sof.SolarCheck=-1 AND sof.SolarOrgId=" + orgId + " ";
            } else {
                return null;
            }
        } else if (actorIds.indexOf("7") >= 0) {//主管审核员
            if (faultStatus == 0) {//待审核
                sql += " WHERE sof.SolarCheck=1 AND sof.SolarMainCheck=0 AND sof.SolarMainOrgId=" + orgId + " ";
            } else if (faultStatus == 1) {//已通过
                sql += " WHERE sof.SolarCheck=1 AND sof.SolarMainCheck=1 AND sof.SolarMainOrgId=" + orgId + " ";
            } else if (faultStatus == 2) {//未通过
                sql += " WHERE sof.SolarCheck=1 AND sof.SolarMainCheck=-1 AND sof.SolarMainOrgId=" + orgId + " ";
            } else {
                return null;
            }
        }
        sql += " ORDER BY sof.SolarTime DESC ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SolarEnergyFaultDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }

    public List<SolarEnergyFaultDto> getSolarFaultHandleList4App(Integer orgId, Integer maintenanceStatus, String actorIds, Integer count, Integer size) throws Exception {
        String sql = "SELECT sof.SolarFaultId,sof.SolarEnergyId,sof.SolarPhoto,sof.SolarType,sof.SolarLevel,sof.SolarReason," +
                "sof.SolarMaintenanceStatus,sof.SolarMainCheckTime,sof.SolarMaintenancePhoto,sof.SolarMaintenanceTime\n" +
                "FROM tb_solar_fault sof " +
                "WHERE sof.SolarMaintenanceStatus=" + maintenanceStatus + "";
        if (actorIds.indexOf("7") >= 0) {
            sql += " AND sof.SolarMainOrgId=" + orgId + "";
        } else if (actorIds.indexOf("5") >= 0) {
            sql += " AND sof.SolarOrgId=" + orgId + "";
        }
        sql += " ORDER BY sof.SolarMainCheckTime DESC ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SolarEnergyFaultDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }

    public List<SolarEnergyFaultDto> getSolarFaultList4App(Integer orgId, String actorIds, Integer faultStatus, Integer count, Integer size) throws Exception {
        String sql = "SELECT sf.SolarPhoto,sf.SolarEnergyId,sf.SolarType,sf.SolarReason,sf.SolarFaultId,sf.SolarLevel,sf.SolarTime,sf.SolarCheck,sf.SolarMainCheck\n" +
                "FROM tb_solar_energy se,tb_solar_fault sf\n" +
                "WHERE se.SolarEnergyId=sf.SolarEnergyId\n";
        //-1:未通过；0：待审核；1：已通过
        if (faultStatus == -1) {
            sql += " AND (sf.SolarCheck=-1 OR sf.SolarMainCheck=-1) ";
        } else if (faultStatus == 0) {
            sql += " AND ((sf.SolarCheck=1 AND sf.SolarMainCheck=0) OR sf.SolarCheck = 0) ";
        } else if (faultStatus == 1) {
            sql += " AND sf.SolarMainCheck = 1 ";
        }
        if (actorIds.indexOf("1") >= 0) {
            sql += "";
        } else {
            sql += " AND sf.SolarOrgId = " + orgId + " ";
        }
        sql += "ORDER BY SolarTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SolarEnergyFaultDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }
}
